
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.io.BufferedReader;
import java.io.Console;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.PrintWriter;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author WSMelnick
 */
public class HousingSelectionServer {

    private static void getType(HousingSelectionServer thisServer) {

        String query = "Select type from DORMS.housingSelection WHERE startTime BETWEEN date_add(NOW(),INTERVAL -60 MINUTE) AND NOW()";
        try {
            ResultSet rset = thisServer.stmt.executeQuery(query);
            while (rset.next()) {
                HousingSelectionServer.type = rset.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(HousingSelectionServer.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private static void getRooms(HousingSelectionServer thisServer) {
        String query = "Select * from DORMS.room WHERE type = '" + HousingSelectionServer.type + "' and isAvail=1 and forFreshman=0";
        try {
            ResultSet rset = thisServer.stmt.executeQuery(query);
            while (rset.next()) {
                int buildingID = rset.getInt("buildingID");
                int floor = rset.getInt("floor");
                int roomNumber = rset.getInt("roomNumber");
                int gender = rset.getInt("gender");
                int roomType = rset.getInt("type");
                boolean isAvail = rset.getBoolean("isAvail");
                boolean forFreshman = rset.getBoolean("forFreshman");
                String description = rset.getString("description");

                Room newRoom = new Room(buildingID, floor, roomNumber, gender, roomType, isAvail, forFreshman, description);
                HousingSelectionServer.availableRooms.add(newRoom);
            }
        } catch (SQLException ex) {
            Logger.getLogger(HousingSelectionServer.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private static void getGroups(HousingSelectionServer thisServer) {
        try {
            String query = "Select groupID from DORMS.groupLogin WHERE groupType = '" + HousingSelectionServer.type + "' ORDER BY groupLottery";
            ResultSet groupSet = thisServer.stmt.executeQuery(query);
            while (groupSet.next()) {
                HousingSelectionServer.groups.add(groupSet.getInt("groupID"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(HousingSelectionServer.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    private Connection conn;
    private Statement stmt;
    static int type;
    public static ArrayList<Integer> groups = new ArrayList<Integer>();
    public static ArrayList<Room> availableRooms = new ArrayList<Room>();

    public HousingSelectionServer() {
        connect();
    }

    public ArrayList<Room> getAvailableRooms() {
        return availableRooms;
    }

    public void setAvailableRooms(ArrayList<Room> availableRooms) {
        HousingSelectionServer.availableRooms = availableRooms;
    }

    public ArrayList<Integer> getGroups() {
        return groups;
    }

    public void setGroups(ArrayList<Integer> groups) {
        HousingSelectionServer.groups = groups;
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        HousingSelectionServer thisServer = new HousingSelectionServer();
        getType(thisServer);
        getRooms(thisServer);
        getGroups(thisServer);

        try {

            ServerSocket serverSocket = new ServerSocket(8000);
            ExecutorService executor = Executors.newCachedThreadPool();

            while (true) {
                // Listen for a new connection request
                Socket connectToClient = serverSocket.accept();
                System.out.println("new client socket");


                // Create a new thread for the connection
                HandleAClient handler = new HandleAClient(connectToClient, type, availableRooms, groups);
                executor.execute(handler);
            }

        } catch (IOException ex) {
            System.err.println(ex);
        }

    }

    public void connect() {
        try {
            // Load the JDBC driver
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded");

            // Establish a connection
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/DORMS?user=root&password=dorms");
            System.out.println("Database connected");

            // Create a Statement
            stmt = (Statement) conn.createStatement();

        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

class HandleAClient implements Runnable {

    private Connection conn;
    private Statement stmt;
    private Socket s;
    boolean active = true;
    ObjectInputStream in;
    ObjectOutputStream out;
    private int groupID;
    private String groupPassword;
    ArrayList<Room> roomsList;
    ArrayList<Integer> groupsList;
    int type;
    ArrayList<Integer> idsList = new ArrayList<Integer>();
    StringBuffer memberString;

    public HandleAClient(Socket socket, int type, ArrayList<Room> roomsList, ArrayList<Integer> groupsList) {
        s = socket;
        this.roomsList = roomsList;
        this.groupsList = groupsList;
        this.type = type;
    }

    @Override
    public void run() {
        connect();
        try {
            out = new ObjectOutputStream(s.getOutputStream());
            in = new ObjectInputStream(s.getInputStream());

            while (active) {
                int request = in.readInt();
                switch (request) {

                    case 1:
                        logIn();
                        break;
                    case 2:
                        getGroupDifference();
                        break;
                    case 3:
                        getAvailableRooms();
                        break;
                    case 4:
                        makeSelection();
                        break;
                    case 5:
                        getSelectionType();
                        break;
                    case 6:
                        dropConnection();
                        break;
                    case 7:
                        getMembers();
                        break;
                }
            }
        } catch (IOException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);

        }
    }

    private void dropConnection() {
        try {
            in.close();
            out.close();
        } catch (IOException e) {
            // Do nothing
        }
    }

    private void logIn() throws IOException, IOException {
        try {
            int id = Integer.parseInt(in.readObject().toString());
            String pw = in.readObject().toString();
            try {
                ResultSet result = stmt.executeQuery("Select * from DORMS.grouplogin where groupID= '" + id + "' and groupPassword= '" + pw + "'");
                if (result.next()) {
                    out.writeInt(1);
                    out.flush();
                    groupID = id;
                    groupPassword = pw;
                } else {
                    out.writeInt(0);
                    out.flush();
                }
                ResultSet ids = stmt.executeQuery("Select userID from DORMS.groupregistration where groupID ='" + groupID + "'");
                while (ids.next()) {
                    idsList.add(ids.getInt(1));
                    System.out.println("logged in with id " + ids.getInt(1));
                }
            } catch (SQLException ex) {
                Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
            }

        } catch (IOException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
            dropConnection();
        } catch (ClassNotFoundException ex) {
                Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);

        }
    }

    private void getGroupDifference() {
        int difference = groupsList.indexOf(groupID);
        try {
            out.writeInt(difference);
        } catch (IOException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void getAvailableRooms() {
        try {
            out.writeObject(roomsList);
        } catch (IOException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void makeSelection() {
        try {
            Room pickedRoom = (Room) in.readObject();
            //add assignment to db
            for (int i = 1; i < idsList.size(); i++) {
                Assignment newAssignment = new Assignment(pickedRoom.roomID, idsList.get(i));
                newAssignment.addToDB(conn);
            }
            //remove room from available rooms list
            roomsList.remove(pickedRoom);

        } catch (IOException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void connect() {
        try {
            // Load the JDBC driver
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded");

            // Establish a connection
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/DORMS?user=root&password=dorms");
            System.out.println("Database connected");

            // Create a Statement
            stmt = (Statement) conn.createStatement();

        } catch (Exception ex) {
            System.out.println(ex);
        }
        System.out.println("connected");    

    }

    private void getSelectionType() {
        try {
            out.writeInt(type);
        } catch (IOException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void getMembers() {
        try {
            ResultSet members = stmt.executeQuery("Select userID from DORMS.groupregistration where groupID ='" + groupID + "'");
            if (members.next()) {
                ResultSet memberName = stmt.executeQuery("Select firstName, lastName from DORMS.student where studentID= '" + members.getInt(1) + "'");
                memberString.append(memberName.getString(1)).append(",");
            }
            out.writeObject(memberString.toString());

        } catch (IOException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(HandleAClient.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
